/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package telsoft.cms_admin.data;


import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import telsoft.admin.util.AppServer;
import telsoft.cms_admin.entity.ETT_FormSystem;
import telsoft.sql.Database;
import telsoft.util.StringUtil;

/**
 *
 * @author hoannc
 */
public class DATA_FormSystem implements Serializable {

    private String SysCode;
    private String SysName;
    private String SysId;
    private String Status;
    private List<String> CharMode = new ArrayList<String>();
    private List<String> ContentMode = new ArrayList<String>();
    private Connection cn = null;
    private PreparedStatement stmt = null;
    private ResultSet rs = null;

    public List<ETT_FormSystem> search() throws Exception {
        String strSQL = " select a.system_id, a.system_code, a.system_name ,a.Status, b.charging_mode,c.content_mode "
                + " from sdp_systems a,sdp_system_charging_mode b,sdp_system_content_mode c "
                + " where a.system_id = b.system_id(+) and a.system_id = c.system_id(+) ";
        Vector vtParam = new Vector();

        if (!SysCode.equals("")) {
            strSQL += " and  a.system_code like upper('%'||?||'%') ";
            vtParam.addElement(SysCode);
        }
        if (!SysName.equals("")) {
            strSQL += " and a.system_name like upper('%'||?||'%') ";
            vtParam.addElement(SysName);
        }
        if (!Status.equals("")) {
            strSQL += " and a.Status = ? ";
            vtParam.addElement(Status);
        }
        strSQL += " order by a.system_code ";
        List<ETT_FormSystem> lstData = new ArrayList<ETT_FormSystem>();
        try {
//            open();
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            for (int i = 0; i < vtParam.size(); i++) {
                stmt.setString(i + 1, vtParam.elementAt(i).toString());
            }
            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_FormSystem category = new ETT_FormSystem();
                category.setSYS_ID(StringUtil.nvl(rs.getString(1), ""));
                if (!rs.getString(1).equals("") && rs.getString(1) != null) {
                    //
                    strSQL = " SELECT a.charging_mode "
                            + " FROM sdp_system_charging_mode a where a.system_id = " + rs.getString(1) + "";
                    PreparedStatement prstmt = cn.prepareStatement(strSQL);
                    ResultSet rst = prstmt.executeQuery();
                    Vector vtData = new Vector();
                    while (rst.next()) {
                        vtData.add(rst.getString(1));
                    }
                    category.setCHAR_MODE(vtData);
                    Database.closeObject(rst);
                    Database.closeObject(prstmt);
                    //
                    strSQL = " SELECT a.content_mode "
                            + " FROM sdp_system_content_mode a where a.system_id = " + rs.getString(1) + "";
                    prstmt = cn.prepareStatement(strSQL);
                    rst = prstmt.executeQuery();
                    vtData = new Vector();
                    while (rst.next()) {
                        vtData.add(rst.getString(1));
                    }
                    category.setCONTENT_MODE(vtData);
                    Database.closeObject(rst);
                    Database.closeObject(prstmt);
                }
                category.setSYS_CODE(StringUtil.nvl(rs.getString(2), ""));
                category.setSYS_NAME(StringUtil.nvl(rs.getString(3), ""));
                category.setSTATUS(StringUtil.nvl(rs.getString(4), ""));
//                category.setCHAR_MODE(vtParam)STATUS(StringUtil.nvl(rs.getString(4), ""));
                lstData.add(category);
            }
        } finally {
            Database.closeObject(rs);
            Database.closeObject(stmt);
            Database.closeObject(cn);
//            close();
        }
        return lstData;
    }

    public String insert() throws Exception {
        String strSQL =
                " insert into sdp_systems a(a.system_id,a.system_code,a.system_name,a.status) "
                + " values(?,?,?,?)";
        try {
//            open();
            cn = AppServer.getConnection();
            SysId = Database.getSequenceValue(cn, "SEQ_SYSTEM");
            stmt = cn.prepareStatement(strSQL);
            stmt.setString(1, SysId);
            stmt.setString(2, SysCode);
            stmt.setString(3, SysName);
            stmt.setString(4, Status);
            stmt.execute();
//            Database.closeObject(rs);
            Database.closeObject(stmt);
            /////////INSERT CONTENT MODE
            strSQL = " insert into sdp_system_content_mode (system_id, content_mode) values(?,?) ";
            cn.setAutoCommit(false);
            stmt = cn.prepareStatement(strSQL);
            for (int i = 0; i < ContentMode.size(); i++) {
                stmt.setString(1, SysId);
                stmt.setString(2, ContentMode.get(i));
                stmt.addBatch();
                stmt.executeBatch();
                cn.commit();
            }
//            cn.setAutoCommit(true);
            Database.closeObject(stmt);
            /////////INSERT CHARGING MODE
            strSQL = " insert into sdp_system_charging_mode (system_id, charging_mode) values(?,?) ";
            cn.setAutoCommit(false);
            stmt = cn.prepareStatement(strSQL);
            for (int i = 0; i < CharMode.size(); i++) {
                stmt.setString(1, SysId);
                stmt.setString(2, CharMode.get(i));
                stmt.addBatch();
                stmt.executeBatch();
                cn.commit();
            }
            cn.setAutoCommit(true);
            Database.closeObject(stmt);
        } finally {
//            Database.closeObject(rs);
//            Database.closeObject(stmt);
//            close();
            Database.closeObject(cn);
        }
        return SysId;
    }

    public void update() throws Exception {
        String strSQL =
                " update sdp_systems a set a.system_code = ?, a.system_name = ?, a.status = ? "
                + " where a.system_id = ? ";
        try {
//            open();
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            stmt.setString(1, SysCode);
            stmt.setString(2, SysName);
            stmt.setString(3, Status);
            stmt.setString(4, SysId);
            stmt.execute();
            Database.closeObject(stmt);
            //
            strSQL = " insert into sdp_system_content_mode (system_id, content_mode) values(?,?) ";
            cn.setAutoCommit(false);
            stmt = cn.prepareStatement(strSQL);
            for (int i = 0; i < ContentMode.size(); i++) {
                stmt.setString(1, SysId);
                stmt.setString(2, ContentMode.get(i));
                stmt.addBatch();
                stmt.executeBatch();
                cn.commit();
            }
//            cn.setAutoCommit(true);
            Database.closeObject(stmt);
            /////////INSERT CHARGING MODE
            strSQL = " insert into sdp_system_charging_mode (system_id, charging_mode) values(?,?) ";
            cn.setAutoCommit(false);
            stmt = cn.prepareStatement(strSQL);
            for (int i = 0; i < CharMode.size(); i++) {
                stmt.setString(1, SysId);
                stmt.setString(2, CharMode.get(i));
                stmt.addBatch();
                stmt.executeBatch();
                cn.commit();
            }
            cn.setAutoCommit(true);
            Database.closeObject(stmt);
        } finally {
//            Database.closeObject(rs);
//            Database.closeObject(stmt);
//            close();
            Database.closeObject(cn);
        }
    }

    public void delete() throws Exception {
        String strSQL =
                " delete from sdp_systems where system_id = ? ";
        try {
//            open();
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            stmt.setString(1, SysId);
            stmt.executeUpdate();
        } finally {
//            Database.closeObject(rs);
            Database.closeObject(stmt);
//            close();
            Database.closeObject(cn);
        }
    }

    public void remove() throws Exception {
        String strSQL = " delete from sdp_system_charging_mode where charging_mode = ? and system_id = ? ";
        try {
//            open();
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            for (int i = 0; i < CharMode.size(); i++) {
                stmt.setString(1, CharMode.get(i));
                stmt.setString(2, SysId);
                stmt.execute();
            }
            Database.closeObject(stmt);
            ///
            strSQL = " delete from sdp_system_content_mode where content_mode = ? and system_id = ? ";
            cn.setAutoCommit(false);
            stmt = cn.prepareStatement(strSQL);
            for (int i = 0; i < ContentMode.size(); i++) {
                stmt.setString(1, ContentMode.get(i));
                stmt.setString(2, SysId);
                stmt.addBatch();
                stmt.executeBatch();
                cn.commit();
            }
            cn.setAutoCommit(true);
            Database.closeObject(stmt);
        } finally {
//            Database.closeObject(rs);
//            close();
            Database.closeObject(cn);
        }
    }

    public void fillParameter(ETT_FormSystem event) {
        SysId = StringUtil.nvl(event.getSYS_ID(), "");
        SysCode = StringUtil.nvl(event.getSYS_CODE(), "");
        SysName = StringUtil.nvl(event.getSYS_NAME(), "");
        Status = StringUtil.nvl(event.getSTATUS(), "");
        ContentMode = event.getCONTENT_MODE();
        CharMode = event.getCHAR_MODE();
    }
}
